﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class PassengerDAL
    {
        private const string SQL_SELECT_WS_PASSENGER = "select PSG_CUSTOMER_ID as CUSTOMER_ID,PSG_PASSENGER_ID AS PASSENGER_ID,PSG_PASSENGER_NAME as PASSENGER_NAME,PSG_CHINESE_SPELL as CHINESE_SPELL,PSG_MOBILE_NUMBER as MOBILE_NUMBER,PSG_ID_TYPE_ID as ID_TYPE_ID,PSG_ID_NUMBER as ID_NUMBER,DATE_FORMAT(PSG_EXPIRY_DATE,'%Y-%m-%d') as EXPIRY_DATE,PSG_RCRE_STAFF_ID as RCRE_STAFF_ID,PSG_RCRE_TIME as RCRE_TIME,PSG_LCHG_STAFF_ID as LCHG_STAFF_ID,PSG_LCHG_TIME as LCHG_TIME,(select IDT_TYPE_NAME from WS_ID_TYPE where IDT_TYPE_ID=PSG_ID_TYPE_ID) as ID_TYPE_NAME,PSG_BIRTHDAY as BIRTHDAY,PSG_NATION_ID as NATION_ID,(select NAT_NATION_NAME from WS_NATION where NAT_NATION_ID=PSG_NATION_ID) as NATION_NAME, PSG_SEX as SEX,(case PSG_SEX when 'F' then '女' else '男' end) as SEX_NAME from WS_PASSENGER where 1=1";
        private const string SQL_INSERT_WS_PASSENGER = "insert into WS_PASSENGER(PSG_CUSTOMER_ID,PSG_PASSENGER_ID,PSG_PASSENGER_NAME,PSG_CHINESE_SPELL,PSG_MOBILE_NUMBER,PSG_ID_TYPE_ID,PSG_ID_NUMBER,PSG_EXPIRY_DATE,PSG_RCRE_STAFF_ID,PSG_RCRE_TIME,PSG_BIRTHDAY,PSG_NATION_ID,PSG_SEX) values(@CustomerId,@PassengerId,@PassengerName,@ChineseSpell,@MobileNumber,@IDTypeId,@IDNumber,@ExpiryDate,@LastStaffId,@LastTime,@Birthday,@NationId,@Sex)";
        private const string SQL_UPDATE_WS_PASSENGER = "update WS_PASSENGER set PSG_PASSENGER_NAME=@PassengerName,PSG_CHINESE_SPELL=@ChineseSpell,PSG_MOBILE_NUMBER=@MobileNumber,PSG_ID_TYPE_ID=@IDTypeId,PSG_ID_NUMBER=@IDNumber,PSG_EXPIRY_DATE=@ExpiryDate,PSG_LCHG_STAFF_ID=@LastStaffId,PSG_LCHG_TIME=@LastTime,PSG_BIRTHDAY=@Birthday,PSG_NATION_ID=@NationId,PSG_SEX=@Sex where 1=1";
        private const string SQL_DELETE_WS_PASSENGER = "delete from WS_PASSENGER where 1=1";
        private const string SQL_COUNT_WS_PASSENGER = "select count(PSG_PASSENGER_ID) from WS_PASSENGER where 1=1";

        private const string SQL_WHERE_CUSTOMER_ID = " and PSG_CUSTOMER_ID=@CustomerId ";
        private const string SQL_WHERE_PASSENGER_ID = " and PSG_PASSENGER_ID=@PassengerId ";
        private const string SQL_WHERE_PASSENGER_NAME = " and PSG_PASSENGER_NAME=@PassengerName ";
        private const string SQL_WHERE_ID_TYPE_ID = " and PSG_ID_TYPE_ID=@IDTypeId ";
        private const string SQL_WHERE_PASSENGER_ID_NOT_EQUAL = " and PSG_PASSENGER_ID<>@PassengerId ";
        private const string SQL_WHERE_ID_NUMBER = " and PSG_ID_NUMBER=@IDNumber ";

        private const string PARM_CUSTOMER_ID = "@CustomerId";
        private const string PARM_PASSENGER_ID = "@PassengerId";
        private const string PARM_PASSENGER_NAME = "@PassengerName";
        private const string PARM_CHINESE_SPELL = "@ChineseSpell";
        private const string PARM_MOBILE_NUMBER = "@MobileNumber";
        private const string PARM_ID_TYPE_ID = "@IDTypeId";
        private const string PARM_ID_NUMBER = "@IDNumber";
        private const string PARM_EXPIRY_DATE = "@ExpiryDate";
        private const string PARM_LAST_STAFF_ID = "@LastStaffId";
        private const string PARM_LAST_TIME = "@LastTime";
        private const string PARM_BIRTHDAY = "@Birthday";
        private const string PARM_NATION_ID = "@NationId";
        private const string PARM_SEX = "@Sex";

        Nullable<DateTime> NullDateTime = null;
        private static PassengerDAL instance = new PassengerDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static PassengerDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(string customerId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_PASSENGER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = customerId });

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public List<PassengerModel> Gets(string customerId)
        {
            using (DataSet ds = this.GetDataSet(customerId))
            {
                DataTable dt = ds.Tables[0];
                List<PassengerModel> list = dt.AsEnumerable().Select(t => new PassengerModel
                {
                    CustomerId = Convert.ToString(t.ItemArray[0]),
                    PassengerId = Convert.ToString(t.ItemArray[1]),
                    PassengerName = Convert.ToString(t.ItemArray[2]),
                    ChineseSpell = Convert.ToString(t.ItemArray[3]),
                    MobileNumber = Convert.ToString(t.ItemArray[4]),
                    IDTypeId = Convert.ToString(t.ItemArray[5]),
                    IDNumber = Convert.ToString(t.ItemArray[6]),
                    ExpiryDate = Convert.ToDateTime(t.ItemArray[7]),
                    CreatedStaffId = Convert.ToString(t.ItemArray[8]),
                    CreatedDate = Convert.ToDateTime(t.ItemArray[9]),
                    LastModifiedStaffId = t.IsNull(10) ? string.Empty : Convert.ToString(t.ItemArray[10]),
                    LastModifiedDate = t.IsNull(11) ? NullDateTime : Convert.ToDateTime(t.ItemArray[11]),
                    Birthday = t.IsNull(13) ? NullDateTime : Convert.ToDateTime(t.ItemArray[13]),
                    NationId = t.IsNull(14) ? string.Empty : Convert.ToString(t.ItemArray[14]),
                    Sex = t.IsNull(16) ? "M" : Convert.ToString(t.ItemArray[16])
                }).ToList<PassengerModel>();

                return list;
            }
        }
        public int Insert(DbTransaction trans, PassengerModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerId });
            parms.Add(new MySqlParameter(PARM_PASSENGER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.PassengerId });
            parms.Add(new MySqlParameter(PARM_PASSENGER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.PassengerName });
            parms.Add(new MySqlParameter(PARM_CHINESE_SPELL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ChineseSpell });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
            parms.Add(new MySqlParameter(PARM_ID_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDTypeId });
            parms.Add(new MySqlParameter(PARM_ID_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDNumber });
            parms.Add(new MySqlParameter(PARM_EXPIRY_DATE, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.ExpiryDate });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });
            parms.Add(new MySqlParameter(PARM_BIRTHDAY, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.Birthday });
            parms.Add(new MySqlParameter(PARM_NATION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.NationId });
            parms.Add(new MySqlParameter(PARM_SEX, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Sex });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_PASSENGER, parms.ToArray());
        }
        public int Update(DbTransaction trans, PassengerModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_PASSENGER);
            sbSQL.Append(SQL_WHERE_PASSENGER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_PASSENGER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.PassengerId });
            parms.Add(new MySqlParameter(PARM_PASSENGER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.PassengerName });
            parms.Add(new MySqlParameter(PARM_CHINESE_SPELL, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ChineseSpell });
            parms.Add(new MySqlParameter(PARM_MOBILE_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MobileNumber });
            parms.Add(new MySqlParameter(PARM_ID_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDTypeId });
            parms.Add(new MySqlParameter(PARM_ID_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDNumber });
            parms.Add(new MySqlParameter(PARM_EXPIRY_DATE, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.ExpiryDate });
            parms.Add(new MySqlParameter(PARM_LAST_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.LastModifiedStaffId });
            parms.Add(new MySqlParameter(PARM_LAST_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LastModifiedDate });
            parms.Add(new MySqlParameter(PARM_BIRTHDAY, MySqlDbType.Date) { Direction = ParameterDirection.Input, Value = model.Birthday });
            parms.Add(new MySqlParameter(PARM_NATION_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.NationId });
            parms.Add(new MySqlParameter(PARM_SEX, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Sex });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Delete(DbTransaction trans, string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_DELETE_WS_PASSENGER);
            sbSQL.Append(SQL_WHERE_PASSENGER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_PASSENGER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_PASSENGER);
            sbSQL.Append(SQL_WHERE_PASSENGER_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_PASSENGER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool Exist(PassengerModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_PASSENGER);
            sbSQL.Append(SQL_WHERE_CUSTOMER_ID);
            sbSQL.Append(SQL_WHERE_PASSENGER_ID_NOT_EQUAL);
            sbSQL.Append(SQL_WHERE_PASSENGER_NAME);
            sbSQL.Append(SQL_WHERE_ID_NUMBER);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_CUSTOMER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.CustomerId });
            parms.Add(new MySqlParameter(PARM_PASSENGER_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.PassengerId });
            parms.Add(new MySqlParameter(PARM_PASSENGER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.PassengerName });
            parms.Add(new MySqlParameter(PARM_ID_NUMBER, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IDNumber });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool NameExist(string name)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_PASSENGER);
            sbSQL.Append(SQL_WHERE_PASSENGER_NAME);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_PASSENGER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = name });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
        public bool IDTypeUsing(string idTypeId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_PASSENGER);
            sbSQL.Append(SQL_WHERE_ID_TYPE_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_ID_TYPE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = idTypeId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
